import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class demo {
    public static void main(String[] args) throws SQLException {
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/java110?allowPublicKeyRetrieval=true&characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("123456");

        Connection connection =dataSource.getConnection();

        String SQL="create table if exist category(id int(11) PRIMARY KEY AUTO_INCREMENT, name varchar(20))";
        PreparedStatement statement =connection.prepareStatement(SQL);
        int n =statement.executeUpdate();
        System.out.println("创建category,n="+n);

        SQL="insert into category values (1, '历史'),(2, '艺术'),(3, '计算机'),(4, '数学'),(5, '小说')";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("插入信息，n="+n);

        SQL="create table if exist student (id int(11) PRIMARY KEY AUTO_INCREMENT,name varchar(20))";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("创建student,n="+n);

        SQL="INSERT INTO student VALUES (1, '王昭君'),(2, '李白'),(3, '貂蝉'),(4, '小乔'),(5, '韩信')";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("插入信息，n="+n);

        SQL="CREATE TABLE  if exist book (id int(11) PRIMARY KEY AUTO_INCREMENT,name varchar(20),author varchar(20),price decimal(10, 2),category_id int(11),CONSTRAINT fk_book_category_id FOREIGN KEY (category_id) REFERENCES category(id))";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("创建book,n="+n);

        SQL="INSERT INTO book VALUES (1, '深入理解Java虚拟机', '周志明', 57.90, 3),(2, '西游记', '吴承恩', 30.68, 5),(3, '儒林外史', '吴敬梓', 18.80, 5),(4, '聊斋志异', '蒲松龄', 21.00, 5),(5, '史记', '司马迁', 278.20, 1),(6, '资治通鉴', '司马光', 524.00, 1)," +
                "(7, 'Java核心技术 卷I：基础', 'Cay S. Horstmann', 92.50, 3),(8, 'Java核心技术卷II：高级特性', 'Cay S. Horstmann', 111.20, 3),(9, 'Java多线程编程核心技术', '高洪岩', 47.50, 3),(10, '诗经', '孔子', 22.00, 2),(11, '唐诗三百首', '蘅塘居士', 49.30, 2)," +
                "(12, '唐诗三百首', '蘅塘居士', 55.00, 2),(13, '西游记', '吴承恩', 47.50, 5),(14, '唐诗三百首', '蘅塘居士', 56.50, 2)";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("插入信息，n="+n);

        SQL="CREATE TABLE if exist borrow_info ( " +
                "id int(11) PRIMARY KEY AUTO_INCREMENT," +
                " book_id int(11)," +
                " student_id int(11)," +
                " start_time timestamp null," +
                " end_time timestamp null," +
                " CONSTRAINT fk_borrow_info_book_id FOREIGN KEY (book_id) REFERENCES book (id)," +
                " CONSTRAINT fk_borrow_info_student_id FOREIGN KEY (student_id) REFERENCES student (id))";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("创建borrow_info,n="+n);

        SQL="INSERT INTO borrow_info VALUES (1, 1, 1, '2018-11-07 18:50:43', '2018-12-07 18:51:01'),(2, 7, 1, '2019-07-10 10:21:00', '2019-09-10 10:21:00')," +
                " (3, 8, 1, '2019-09-10 10:21:00', '2019-10-10 10:21:00'),(4, 2, 2, '2019-03-02 16:37:00', '2019-04-02 16:37:00'),(5, 4, 2, '2019-03-12 14:25:00', '2019-04-12 14:25:00')," +
                "(6, 10, 2, '2019-07-13 16:21:00', '2019-10-13 16:21:00'),(7, 11, 2, '2019-06-09 09:40:00', '2019-07-09 09:40:00'),(8, 13, 2, '2019-01-03 15:11:00', '2019-04-03 15:11:00')," +
                "(9, 7, 3, '2019-05-15 13:13:00', '2019-06-15 13:13:00'),(10, 8, 3, '2019-04-27 13:53:00', '2019-05-27 13:53:00'),(11, 9, 3, '2019-06-01 11:32:00', '2019-07-01 11:32:00')," +
                "(12, 3, 4, '2019-07-01 09:40:00', '2019-08-01 09:40:00'),(13, 4, 4, '2019-06-19 11:40:00', '2019-07-19 11:40:00'),(14, 5, 4, '2019-06-25 09:23:00', '2019-09-25 09:23:00')," +
                " (15, 10, 4, '2019-08-27 15:30:00', '2019-09-27 15:30:00'),(16, 5, 5, '2019-01-23 14:20:00', '2019-04-23 14:20:00'),(17, 6, 5, '2019-03-09 10:45:00', '2019-04-09 10:45:00')," +
                "(18, 10, 5, '2019-06-17 11:32:00', '2019-09-17 11:32:00')";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("插入信息，n="+n);

        SQL="INSERT INTO borrow_info VALUES (19, 10, 3, '2019-09-25 17:50:43', '2019-10-25 17:50:01')";
        statement =connection.prepareStatement(SQL);
        n =statement.executeUpdate();
        System.out.println("插入信息，n="+n);

        SQL="select book.name,book.author,book.price from book,category where book.category_id=category.id and category.name='计算机'";
        statement =connection.prepareStatement(SQL);
        ResultSet resultSet =statement.executeQuery();

        while(resultSet.next()){
            String name=resultSet.getString("name");
            String author=resultSet.getString("author");
            double price=resultSet.getDouble("price");
            System.out.println("name="+name+",author="+author+",price="+price);
        }

        statement.close();
        connection.close();

    }
}
